package JDBC_Test.Student.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

//import com.mysql.cj.jdbc.JdbcConnection;

import JDBC_Test.Student.JDBCUtil;
import JDBC_Test.Student.Model.StudentModel;

public class StudentDao {

	Connection con = null;
	Statement statement = null;
	PreparedStatement ps = null;

	// 插入一条学生信息
	public int insert(StudentModel studentModel) throws SQLException {
		String sql = "insert into students(sname,sage,ssex,sno,sdept) " + "values(?,?,?,?,?)";
		try {
			con = JDBCUtil.geConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, studentModel.getName());
			ps.setInt(2, studentModel.getAge());
			ps.setInt(3, studentModel.getSex());
			ps.setString(4, studentModel.getSno());
			ps.setString(5, studentModel.getDept());
			return ps.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, statement, null);
		}
		return ps.executeUpdate();

	}

	/**
	 * 批量插入
	 * 
	 * @param list
	 * @return
	 */
	public int insertList(List<StudentModel> list) {
		String sql = "insert into students(sname,sage,ssex,sno,sdept) " + "values(?,?,?,?,?)";

		try {
			con = JDBCUtil.geConnection();
			con.setAutoCommit(false);// 批量执行sql语句开始前，开启事务
			ps = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				StudentModel studentModel = list.get(i);
				ps.setString(1, studentModel.getName());
				ps.setInt(2, studentModel.getAge());
				ps.setInt(3, studentModel.getSex());
				ps.setString(4, studentModel.getSno());
				ps.setString(5, studentModel.getDept());
				ps.addBatch();// 添加
			}
			int[] i = ps.executeBatch();// 执行
			ps.clearBatch();// 清除
			con.commit();// 批量执行完毕，提交事务
			return i.length;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			try {
				con.rollback();// 出现异常则回滚
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			JDBCUtil.close(con, statement, null);
		}
		return 0;
	}

	/**
	 * 查询全部，可以由下面的selectList方法替代
	 */
	public void selectAll() {
		String sql = "select * from students";
		try {
			con = JDBCUtil.geConnection();
			statement = con.createStatement();
			ResultSet res = statement.executeQuery(sql);
			while (res.next()) {
				String sname = res.getString("sname");
				int sage = res.getInt("sage");
				int sex = res.getInt("ssex");
				String sno = res.getString("sno");
				String sdept = res.getString("sdept");
				if (sex == 0) {
					System.out.println("学生姓名：" + sname + " 年龄：" + sage + " 性别: 男" + " 学号:" + sno + " 专业:" + sdept);
				} else if (sex == 1) {
					System.out.println("学生姓名：" + sname + " 年龄：" + sage + " 性别: 女" + " 学号:" + sno + " 专业:" + sdept);
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, statement, null);
		}
	}

	/**
	 * 动态查询
	 * 
	 * @param studentModel
	 * @return
	 */
	public List<StudentModel> selectList(StudentModel studentModel) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("select ");
		sql.append("sname,sage,ssex,sno,sdept ");
		sql.append("from students where 1=1 ");
		// 进行了查询条件的筛选和sql语句的拼接
		List<Object> list = appendWhere(sql, studentModel);

		List<StudentModel> result = new ArrayList<StudentModel>();
		ResultSet rs = null;
		try {
			con = JDBCUtil.geConnection();// 获得连接
			ps = con.prepareStatement(sql.toString());// 获得预处理状态集
			// 把list中的条件拼接到sql中
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			rs = ps.executeQuery();// 执行sql，获得结果集
			while (rs.next()) {// 映射结果集到result
				StudentModel s = new StudentModel();
				s.setName(rs.getString("sname"));
				s.setAge(rs.getInt("sage"));
				s.setSex(rs.getInt("ssex"));
				s.setSno(rs.getString("sno"));
				s.setDept(rs.getString("sdept"));
				result.add(s);
			}
			// 输出查询结果
			for (int i = 0; i < result.size(); i++) {
				System.out.println(result.get(i).toString());
			}
			return result;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, statement, ps);
		}
		return result;
	}

	/**
	 * 
	 * @param sql
	 * @param studentModel
	 * @return
	 * 
	 *         动态查询的sql拼接和条件筛选
	 */
	private List<Object> appendWhere(StringBuffer sql, StudentModel studentModel) {
		// TODO Auto-generated method stub
		List<Object> list = new ArrayList<Object>();
		String name = studentModel.getName();
		if (name != null) {
			sql.append(" and sname=? ");
			list.add(name);
		}
		Integer age = studentModel.getAge();
		if (age != null) {
			sql.append(" and sage=? ");
			list.add(age);
		}
		Integer sex = studentModel.getSex();
		if (sex != null) {
			sql.append(" and ssex = ? ");///////////////
			list.add(sex);
		}
		String sno = studentModel.getSno();
		if (sno != null) {
			sql.append(" and sno=? ");
			list.add(sno);
		}
		String sdept = studentModel.getDept();
		if (sdept != null) {
			sql.append(" and sdept=? ");
			list.add(sdept);
		}

		return list;
	}

	/**
	 * 删除
	 * 
	 * @param studentModel
	 */
	public void delete(StudentModel studentModel) {
		// TODO Auto-generated method stub
		String sql = "delete from students where sno=?";
		try {
			con = JDBCUtil.geConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, studentModel.getSno());
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, null, ps);
		}

	}

	/**
	 * 修改
	 * 
	 * @param studentModel
	 */
	public void update(StudentModel studentModel) {
		String sql = "update students set sname=?,sage=?,sdept=? where sno = ?";
		try {
			con = JDBCUtil.geConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, studentModel.getName());
			ps.setInt(2, studentModel.getAge());
			ps.setString(3, studentModel.getDept());
			ps.setString(4, studentModel.getSno());
			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		} finally {
			JDBCUtil.close(con, null, ps);
		}

	}

}
